package com.weixin.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.management.Query;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.sun.org.apache.bcel.internal.generic.NEW;
import com.weixin.dao.StudentDao;
import com.weixin.entity.Student;
import com.weixin.entity.User;
import com.weixin.util.DButil;

public class StudentImpl implements StudentDao {
	DButil db=new DButil();
     QueryRunner qr=new QueryRunner(db.getSource());
	@Override
	public List<Student> selsctAll() throws SQLException {
		return qr.query("select * from student s,classroom c where s.c_id=c.c_id", new BeanListHandler<Student>(Student.class));
	}

	@Override
	public Student selectByid(int s_id) throws SQLException {
		return qr.query("select * from student s,classroom c where s.c_id=c.c_id and s_id=?", new BeanHandler<Student>(Student.class),s_id);
	}

	@Override
	public boolean deleteByid(int u_id) throws SQLException {
		return qr.update("delete from user where u_id=?",u_id)>0;
	}

	@Override
	public boolean insert(Student student) throws SQLException {
		return qr.update("insert into student values(0,?,?,?,?,?,?)",student.getName(),student.getGender(),student.getBirthday(),student.getCity(),student.getIntroduction(),student.getC_id())>0;
	}

	@Override
	public boolean update(Student student) throws SQLException {
		return qr.update("update student set name=?,gender=?,birthday=?,city=?,introduction=?,c_id=?",student.getName(),student.getGender(),student.getBirthday(),student.getCity(),student.getIntroduction(),student.getC_id())>0;
	}

	@Override
	public Student selsctByName(String name) throws SQLException {
		return qr.query("select * from student s,classroom c where s.c_id=c.c_id and name=?", new BeanHandler<Student>(Student.class),name);
	}

	@Override
	public List<User> getAllBypage(String role, int pageNum, int pageSize) throws SQLException {
		return qr.query("select * from user where u_id in (select u_id from user_role where r_id in (select r_id from role where title=?)) limit ?,?",new BeanListHandler<User>(User.class),role,(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public int getTotal(String role) throws SQLException {
		return (int)(long)qr.query("select count(*) from user where u_id in (select u_id from user_role where r_id in (select r_id from role where title=?))",new ScalarHandler(),role);
	}

	@Override
	public User select(String name, String password) throws SQLException {
		return qr.query("select * from user where name=? and password=?",new BeanHandler<User>(User.class),name,password);
	}

	@Override
	public List<User> ListByrole(String role) throws SQLException {
		List<User> us =new ArrayList<User>();
		us= qr.query("select * from user where u_id in (select u_id from user_role where r_id in (select r_id from role where title=?))", new BeanListHandler<User>(User.class),role);
        return us;	
	}

	@Override
	public boolean deleteByRole(int u_id) throws SQLException {
		return qr.update("delete from user_role where u_id=?", u_id)>0;
	}

	@Override
	public boolean deleteByAll(List<String> u_ids) throws SQLException {
		StringBuffer sql=new StringBuffer("delete from user where u_id in(");
		for (String string : u_ids) {
			sql.append("?,");	
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		//list转成数组作为update()方法的可变参数:
		Object[] array = u_ids.toArray();
		return qr.update(sql.toString(),array)>0;
	}

	@Override
	public boolean deleteByRoleAll(List<String> u_ids) throws SQLException {
		
		StringBuffer sql=new StringBuffer("delete from user_role where u_id in(");
		for (String string : u_ids) {
			sql.append("?,");	
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		//list转成数组作为update()方法的可变参数:
		Object[] array = u_ids.toArray();
		return qr.update(sql.toString(),array)>0;
	}

	@Override
	public List<User> selectSearch(String role, String search, int pageNum, int pageSize) throws SQLException {
		String sql= "select * \r\n" + 
				"from (select user.*,role.title\r\n" + 
				"from user left join user_role\r\n" + 
				"on user.u_id=user_role.u_id\r\n" + 
				"left join role\r\n" + 
				"on user_role.r_id=role.r_id)temp\r\n" + 
				"where title=? and concat(ifnull(u_id,''),ifnull(name,''),ifnull(birthday,''),ifnull(phone,'')) like ? "+ 
				"limit ?,?";
		return qr.query(sql,new BeanListHandler<User>(User.class),role,"%"+search+"%",(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public int selectTotalSearch(String role, String search) throws SQLException {
		String sql = "select count(*) \r\n" + 
				"from (select user.*,role.title\r\n" + 
				"	from user left join user_role\r\n" + 
				"	on user.u_id=user_role.u_id\r\n" + 
				"	left join role\r\n" + 
				"	on user_role.r_id=role.r_id)temp\r\n" + 
				"where title=? and concat(ifnull(u_id,''),ifnull(name,''),ifnull(birthday,''),ifnull(phone,'')) like ?";
		return (int)(long)qr.query(sql, new ScalarHandler() ,role,"%"+search+"%");
	}
}
